package com.wyx.jdbc.datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 王艺锡
 * @version 1.0
 * apache-DBUtils + druid的使用
 */
public class DBUtils_Use {

    //apache-DBUtils + druid查询多行多列
    @Test
    public void testQueryMany() throws SQLException {
        //1.使用druid得到连接
        Connection connection = JDBCUtils_Druid.getConnection();
        //2.使用DBUtils类和接口，先引入jar包
        //3.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id >= ?";
        //也可以只查询指定列
        //String sql = "select id,name from actor where id >= ?";

        //1.query方法就是执行sql语句，然后得到resultSet，然后将结果集resultSet封装到ArrayList集合中
        //2.返回集合
        //3.connection:连接
        //4.sql:执行的sql语句
        //5.new BeanListHandler<>(Actor.class) :得到resultSet，然后将结果集resultSet封装到ArrayList集合中
        //  底层使用反射机制，去获取Actor类的属性，然后进行封装
        //6. 1:就是给sql语句中第一个?赋值
        //7.底层会得到resultSet,preparedStatement,然后再query方法关闭时，关闭


        //BeanListHandler用于查询多行多列的语句，并且返回一个集合
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);

        for (Actor actor : list) {
            System.out.println(actor);
        }

        JDBCUtils_Druid.close(null, null, connection);
    }


    @Test
    ////apache-DBUtils + druid查询单行多列
    public void testQuerySingle() throws SQLException {
        Connection connection = JDBCUtils_Druid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from actor where id = ?";
        // String sql = "select id,name from actor where id = ?";

        //BeanHandler用于查询单行多列的语句，返回一个javaBean对象
        Actor actor = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 2);
        System.out.println(actor);

        JDBCUtils_Druid.close(null, null, connection);

    }

    //apache-DBUtils + druid查询单行单列
    @Test
    public void Scalar() throws SQLException {
        Connection connection = JDBCUtils_Druid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select name from actor where id = ?";
        //ScalarHandler用于查询单行单列的数据，返回一个object对象
        Object obj = queryRunner.query(connection, sql, new ScalarHandler<>(), 1);
        System.out.println(obj);
        JDBCUtils_Druid.close(null, null, connection);

    }


    //apache-DBUtils + druid完成dml语句
    @Test
    public void dml() throws SQLException {
        Connection connection = JDBCUtils_Druid.getConnection();
        QueryRunner queryRunner = new QueryRunner();
        //String sql1 = "update actor set phone = ? where id = ?";
        //String sql2 = "insert into actor values(null,'林俊杰','男','1989-10-12','16546854')";
        String sql3 = "delete from actor where id = ?";
        int rows = queryRunner.update(connection, sql3,4);

        System.out.println(rows > 0 ? "更改了表" : "没有修改表");
        JDBCUtils_Druid.close(null,null,connection);
    }

}
